"""分析BI每日考勤表，得出PMG的迟到早退等情况"""

from datetime import datetime, time, date
import pandas as pd
from pathlib import Path
import os
import sys

# 获取项目根目录，假设当前文件所在路径结构固定
project_root = Path(__file__).resolve().parent.parent.parent
# 将项目根目录添加到系统路径
sys.path.append(str(project_root))

from entity.daily_attendance import DailyAttendance


class PMGAttendanceAnalyzer:
    """PMG考勤分析器类，用于处理和分析PMG的考勤数据"""

    def __init__(self, excel_path=None):
        """
        初始化PMGAttendanceAnalyzer实例

        参数:
            excel_path: Excel文件路径，如果为None则使用默认路径
        """
        if excel_path is None:
            self.excel_path = os.path.join(project_root, "img", "pmg.xlsx")
        else:
            self.excel_path = excel_path

        # 规则时间
        self.normal_arrival_start = time(6, 0, 0)
        self.normal_arrival_end = time(8, 32, 0)
        self.normal_departure_time = time(17, 28, 0)

        # 在初始化时读取Excel文件并存储数据
        self.raw_data = self._read_excel_file()

    def _read_excel_file(self):
        """
        读取Excel文件并返回原始数据

        返回:
            原始考勤数据DataFrame，如果读取失败则返回空DataFrame
        """
        try:
            # 读取'BI每日考勤' sheet
            df = pd.read_excel(
                self.excel_path,
                sheet_name="BI每日考勤",
                usecols=["user_id", "员工姓名", "打卡时间"],
            )

            # 创建列名映射关系：中文名 -> 英文名
            column_mapping = {
                "user_id": "card_no",
                "员工姓名": "name",
                "打卡时间": "entry_time",
            }
            # 执行列名转换
            df = df.rename(columns=column_mapping)
            # 确保 entry_time 是 datetime 对象
            df["entry_time"] = pd.to_datetime(df["entry_time"])
            df["entry_date"] = df["entry_time"].dt.date

            return df
        except Exception as e:
            print(f"读取Excel考勤数据时发生错误: {e}")
            return pd.DataFrame()

    def fetch_pmg_records(self, start_date: datetime, end_date: datetime):
        """
        从已加载的数据中筛选指定日期范围的考勤记录

        参数:
            start_date: 开始日期
            end_date: 结束日期

        返回:
            筛选后的考勤记录DataFrame
        """
        # 从已加载的数据中筛选
        mask = (
            (self.raw_data["entry_date"] >= start_date.date())
            & (self.raw_data["entry_date"] <= end_date.date())
            & (self.raw_data["entry_time"].dt.hour >= 6)
            & (self.raw_data["entry_time"].dt.hour < 24)
        )
        filtered_df = self.raw_data[mask].copy()  # 创建副本避免SettingWithCopyWarning

        return filtered_df

    def analyze_records(self, df_records):
        """
        使用 Pandas 分析出勤记录并统计状态

        参数:
            df_records: 考勤记录DataFrame

        返回:
            考勤分析结果列表
        """
        if df_records.empty:
            return []

        # 创建DataFrame的副本以避免SettingWithCopyWarning
        df_records = df_records.copy()

        # 提取日期和时间部分
        df_records["entry_clock_time"] = df_records["entry_time"].dt.time

        # 按工号和日期分组，计算最早和最晚打卡时间
        daily_summary = df_records.groupby(["card_no", "name", "entry_date"])
        daily_summary = (
            daily_summary["entry_clock_time"].agg(["min", "max"]).reset_index()
        )
        daily_summary.rename(
            columns={"min": "arrival_time", "max": "departure_time"}, inplace=True
        )
        daily_attendance = []

        for _, row in daily_summary.iterrows():
            card_no = row["card_no"]
            name = row["name"]
            date_obj = row["entry_date"]
            arrival_time = row["arrival_time"]
            departure_time = row["departure_time"]

            # 判断上班状态
            if arrival_time > self.normal_arrival_end:
                arrival_status = "迟到"
            elif arrival_time < self.normal_arrival_start:
                arrival_status = "过早"
            else:
                arrival_status = "正常"

            # 判断下班状态
            if departure_time <= self.normal_departure_time:
                departure_status = "早退"
            else:
                departure_status = "正常"

            daily_attendance.append(
                DailyAttendance(
                    name=name,
                    card_no=card_no,
                    date=date_obj.isoformat(),
                    arrival_status=arrival_status,
                    arrival_time=arrival_time.strftime("%H:%M:%S"),
                    departure_status=departure_status,
                    departure_time=departure_time.strftime("%H:%M:%S"),
                )
            )

        return daily_attendance

    def fetch_and_analysis(self, start_date: datetime, end_date: datetime):
        """
        整合数据获取和分析功能

        参数:
            start_date: 开始日期
            end_date: 结束日期

        返回:
            考勤分析结果的DataFrame
        """
        records_df = self.fetch_pmg_records(start_date, end_date)
        attendance_summary = self.analyze_records(records_df)

        # 方法：使用__dict__属性（自动获取所有属性）
        ret_df = pd.DataFrame([p.__dict__ for p in attendance_summary])
        return ret_df

    def get_attendance_status_by_name(self, name, date_obj: datetime) -> dict:
        """
        根据姓名和日期查询考勤状态

        参数:
            name: 员工姓名
            date_obj: 查询日期
        返回:
            包含上班状态和下班状态的字典，如果未找到记录则返回None
        """
        ret = {
            "card_no": "",
            "name": name,
            "date": date_obj.isoformat(),
            "arrival_status": "",
            "arrival_time": "",
            "departure_status": "",
            "departure_time": "",
        }
        fetch_and_analysis = self.fetch_and_analysis(date_obj, date_obj)
        if fetch_and_analysis.empty:
            return ret
        mask = fetch_and_analysis["name"] == name
        if mask.sum() == 0:
            return ret
        record = fetch_and_analysis[mask].iloc[0]
        # return record.to_dict()
        # return DailyAttendance(**record)
        return {
            "card_no": record.card_no,
            "name": name,
            "date": date_obj.isoformat(),
            "arrival_status": record.arrival_status,
            "arrival_time": record.arrival_time,
            "departure_status": record.departure_status,
            "departure_time": record.departure_time,
        }


def test_process_excel_attendance():
    """测试Excel考勤数据处理函数"""
    # 使用示例日期
    start_date_str = "2025-08-04"
    end_date_str = "2025-08-09"

    # 创建日期对象
    start_date = datetime.strptime(start_date_str, "%Y-%m-%d")
    end_date = datetime.strptime(end_date_str, "%Y-%m-%d")

    # 创建分析器实例
    analyzer = PMGAttendanceAnalyzer()

    # 测试fetch_and_analysis方法
    df = analyzer.fetch_and_analysis(start_date, end_date)
    print("考勤分析结果：")
    mask = df["arrival_status"] != "正常"
    attendance_records = df[mask]
    # 显示列的宽度
    pd.set_option("display.max_colwidth", 20)
    print(attendance_records)

    # 测试get_attendance_status_by_name方法
    # 注意：这里需要替换为Excel中实际存在的姓名进行测试
    test_name = "彭国安"
    test_date_str = "2025-08-01"
    test_date = datetime.strptime(test_date_str, "%Y-%m-%d")
    status = analyzer.get_attendance_status_by_name(test_name, test_date)
    if status:
        print(f"\n{test_name}在{test_date_str}的考勤状态：")
        print(
            f"上班状态: {status['arrival_status']}, 上班时间: {status['arrival_time']}"
        )
        print(
            f"下班状态: {status['departure_status']}, 下班时间: {status['departure_time']}"
        )
    else:
        print(f"\n未找到{test_name}在{test_date_str}的考勤记录")


if __name__ == "__main__":
    test_process_excel_attendance()
